package com.ruoyi.task;

import com.ruoyi.system.mapper.SysUserMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

import java.sql.*;
import java.util.*;

@Slf4j
@Configuration      //1.主要用于标记配置类，兼备Component的效果。
@EnableScheduling   // 2.开启定时任务
public class MyCron {
    @Autowired
    private SysUserMapper userMapper;

    @Scheduled(cron = "0 0/1 * * * ?")
    public void testsyncshogongshuju(){
        syncoracle(1);
        String url="jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";

        Connection conn = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
//
//            conn = DriverManager.getConnection(url, "root", "root");
//            System.out.println("Connected to the Oracle server 手工数据--======");
//
//            List<Map<String, Object>> maps = userMapper.yuansheng("select * from int_0101_jggqxxb");
//            System.out.println("yuansheng---");
//            System.out.println(maps);
//            if(maps.size() >0) {
//                Map<String, Object> onemap = maps.get(0);
//                Set<String> mapcolumns = onemap.keySet();
//                for (String mapcolumn : mapcolumns) {
//                    System.out.println("mapcolumn:"+mapcolumn);
//                }
//            }


















//            Statement stmt = conn.createStatement();
//            ResultSet rs = stmt.executeQuery("select *from sys_user");
//
//                ResultSetMetaData rsmd = rs.getMetaData();
//                int columnsCount = rsmd.getColumnCount();
//                for (int i = 1; i <= columnsCount; i++) {
//                    String columnName = rsmd.getColumnName(i);
//                    System.out.println(columnName);
//                }


//            // 4. 执行SQL语句
//
//            stmt = conn.prepareStatement(sql);
////            stmt.setString(1, userid);
////            stmt.setString(2, tablename);
//            stmt.setString(1, batchno);
//
//            ResultSet rs = stmt.executeQuery();
//
//            // 封装结果
//            ResultSetMetaData rsmd = rs.getMetaData();
//            int columnCount = rsmd.getColumnCount();
//
//            List<Map<String, Object>> rows = new ArrayList<>();
//            while (rs.next()) {
//                Map<String, Object> row = new HashMap<>();
//                for (int i = 1; i <= columnCount; i++) {
//                    row.put(rsmd.getColumnName(i), rs.getObject(i));
//                }
//                rows.add(row);
//            }
//
//            return rows;

        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC driver not found");
            e.printStackTrace();

        } catch (Exception e) {
            System.out.println("Connection to Oracle failed");
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }


    public void syncoracle(int id) {


        String tablename = "int_0101_jggqxxb";
        String batchno = "20240630";


        Connection conn = null;
        PreparedStatement stmt = null;
        try {
//            Class.forName("oracle.jdbc.driver.OracleDriver");
            Class.forName("com.mysql.cj.jdbc.Driver");
//            conn = DriverManager.getConnection(shougongdb, shougongdbuser, shougongdbpwd);
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8",
                    "root", "root");
            log.info("Connected to the Oracle server 手工数据--======");
            System.out.println("Connected to the Oracle server 手工数据--======");

            String year = batchno.substring(0, 4);
            String month = batchno.substring(4, 6);
            String day = batchno.substring(6, 8);

            String start = year + "-" + month + "-" + day + " 00:00:00";
            String end = year + "-" + month + "-" + day + " 23:59:59";

            String sqlcount = "select count(*) cnt from " + tablename + " where date_id>='" + start + "' and date_id <='" + end + "'";

            int total = userMapper.yuanshengcnt(sqlcount);
            if (total > 0) {
                //--------同步到oracle
                int pageSize = 1;
                int totalPage = total / pageSize;

                if (total % pageSize == 0) {
                    totalPage = total / pageSize;
                } else {
                    totalPage = total / pageSize + 1;
                }


                for (int i = 1; i <= totalPage; i++) {

                    int param_limit = (i-1)*pageSize;

                    String sqlrange = "select * from " + tablename
                            + " where date_id>='" + start + "' and date_id <='" + end + "' limit "+ param_limit+","+ pageSize;

                    List<Map<String, Object>> rangedata = userMapper.yuansheng(sqlrange);
                    System.out.println("sqlrange----");
                    System.out.println(sqlrange);
                    if(rangedata.size() >0 ){
                        System.out.println("rangedata===");
                        System.out.println(rangedata);

                        Map<String, Object> map3 = rangedata.get(0);
                        Set<String> setcols = map3.keySet();  //获得列名
                        List<String> cols = new ArrayList<>(setcols);

                        String sqlbatch= getInsertParams(tablename+"_123",cols,null);
                        PreparedStatement pstmt = conn.prepareStatement(sqlbatch);
                        System.out.println("sqlbatch===");
                        System.out.println(sqlbatch);


                        for (Map<String, Object> item : rangedata) {
                            System.out.println("item==");
                            System.out.println(item);
                            for(int idx=0;idx<cols.size();idx++){
                                Object curvalu = item.get(cols.get(idx));
                                if(curvalu ==null){
                                    curvalu="";

                                }
                                pstmt.setObject(idx+1,curvalu);
                            }
                            pstmt.addBatch();
                        }

                        // 执行批处理
                        pstmt.executeBatch();
                        System.out.println("批量插入执行成功=========");
                        conn.commit(); // 确保事务被提交

                        //--------

                    }

                }



                //--------同步到oracle
            }


//            // 4. 执行SQL语句
//
//            stmt = conn.prepareStatement(sql);
////            stmt.setString(1, userid);
////            stmt.setString(2, tablename);
//            stmt.setString(1, batchno);
//
//            ResultSet rs = stmt.executeQuery();
//
//            // 封装结果
//            ResultSetMetaData rsmd = rs.getMetaData();
//            int columnCount = rsmd.getColumnCount();
//
//            List<Map<String, Object>> rows = new ArrayList<>();
//            while (rs.next()) {
//                Map<String, Object> row = new HashMap<>();
//                for (int i = 1; i <= columnCount; i++) {
//                    row.put(rsmd.getColumnName(i), rs.getObject(i));
//                }
//                rows.add(row);
//            }
//
//            return rows;

        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC driver not found");
            e.printStackTrace();

        } catch (SQLException e) {
            System.out.println("Connection to Oracle failed");
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public Map<String,Object> getTableCols(String tablename){
        Map<String,Object> ans = new HashMap<>();

        if(tablename.equals("int_0101_jggqxxb")){
            String cols =  "gdbh, gdmc,czrq,jgbz, jgzjlx,jgzjhm,grzjlx,grzjhm,dz,zczb,cgbl,czje,zysl,zybl,bz,insert_time,date_id";
            ans.put("cols",cols);
            ans.put("colslist",new ArrayList<>(Arrays.asList(cols.split(","))));
        }
        return ans;
    }

    public String getInsertParams(String tablename,List<String> cols,Object[][] data) {
//        String url = "jdbc:mysql://localhost:3306/yourdatabase";
//        String username = "yourusername";
//        String password = "yourpassword";

        String colnamestr = "";
        String colnamestrwenhao = "";

        for (String col : cols) {
            if(colnamestr.length() >0) {
                colnamestr+=",";
                colnamestrwenhao+=",";
            }
            colnamestr+=col;
            colnamestrwenhao+="?";
        }

        return "INSERT INTO "+tablename+" ("+colnamestr+") VALUES ("+colnamestrwenhao+")";
       /*
        String insertQuery = "INSERT INTO yourtable (column1, column2, ...) VALUES (?, ?, ...);";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(insertQuery)) {

            // 假设我们有一个数据列表，每个元素都是一个数据行的数据列表
//            Object[][] data = {
//                    {"value1_1", "value1_2", ...},
//            {"value2_1", "value2_2", ...},
//            // ... 更多数据行
//            };

            for (Object[] rowData : data) {
                for (int i = 0; i < rowData.length; i++) {
                    pstmt.setObject(i + 1, rowData[i]);
                }
                pstmt.addBatch();
            }

            // 执行批处理
            pstmt.executeBatch();

            conn.commit(); // 确保事务被提交

        } catch (SQLException e) {
            e.printStackTrace();
        }*/
    }
}

/*
CREATE TABLE int_0101_jggqxxb (
                                             gdbh varchar(20) ,
                                             gdmc varchar(200) ,
                                             czrq timestamp(0),
                                             jgbz varchar(10) ,
                                             jgzjlx varchar(30) ,
                                             jgzjhm varchar(50) ,
                                             grzjlx varchar(50) ,
                                             grzjhm varchar(50) ,
                                             dz varchar(200) ,
                                             zczb numeric(16,2),
                                             cgbl numeric(16,6),
                                             czje numeric(16,2),
                                             zysl numeric(16,0),
                                             zybl numeric(16,6),
                                             bz varchar(200) ,
                                             insert_time timestamp(0) ,
                                             date_id timestamp(6)
)

 */


